package o2o.web.actions.statis;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jinyou.utils.common.AjaxResult;
import com.jinyou.utils.common.DateUtils;
import com.jinyou.utils.common.JYPinyinHelper;
import com.jinyou.utils.common.ValidateUtil;
import o2o.cache.agent.CityAgentMemory;
import o2o.cache.agent.PostmanAgentRelationMemory;
import o2o.cache.postman.CityPostmanMemory;
import o2o.cache.user.UserInfoMemory;
import o2o.cache.user.UserOnlineImpl;
import o2o.cache.wallet.UserWalletMemory;
import o2o.config.dao.UserDaoSql;
import o2o.dao.PO.PojoTables;
import o2o.dao.PO.agent.CityAgent;
import o2o.dao.PO.common.UserType;
import o2o.dao.PO.order.ORDER_STATUS;
import o2o.postman.model.CityPostman;
import o2o.dao.PO.postman.POSTMAN_TYPE;
import o2o.dao.PO.user.UserInfo;
import o2o.dao.PO.wallet.UserWallet;
import o2o.dao.VO.order.REFUND_APPLY_STATUS;
import o2o.system.data.SYS_CONFIG_PATH;
import o2o.system.data.SYS_SETTINGS;
import o2o.task.order.OrderBakTaskUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import static com.jinyou.utils.common.AjaxResult.successResult;

/**
 * @Description: TODO
 * Created with IntelliJ IDEA.
 * User: Jahnke [1029777564@qq.com]
 * Date: 2019-02-20 14:17
 * To change this template use File | Settings | File Templates.
 */
@Controller
public class PostmanStatisController {

    /**
     * 【优齐同城】骑手统计，有h5和管理后台-统计信息
     *
     * @param token
     * @param startTime
     * @param endTime
     * @param outTime   单位:秒
     * @return
     */
    @RequestMapping(method = RequestMethod.POST, value = "admin/postman/statistics")
    @ResponseBody
    public String postmanCountStatis(String token, Long startTime, Long endTime, Long outTime) {

        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        if (user.getUserType() != UserType.POSTMAN) {
//            return AjaxResult.errorResult("用户权限错误！");
        }

        if (startTime == null || endTime == null) {
            return AjaxResult.errorResult("请选择查询时间");
        }
        if (outTime == null) {
            return AjaxResult.errorResult("请选择超时时间");
        }
        /*
            SELECT
                SUM(a.postmanMoney),SUM(distance),AVG(ttime),SUM((CASE WHEN ttime>30 THEN 0 ELSE 1 END)) chaoOrides,COUNT(orderNo) counts
            FROM (
                SELECT ori.orderNo,IFNULL(orp.postmanMoney,0) AS postmanMoney,ori.shopLat,ori.shopLng,ori.lat,ori.lng,
                    ROUND(6378.138*2*ASIN(SQRT(POW(SIN((ori.shopLat*PI()/180-ori.lat*PI()/180)/2),2)+COS(ori.shopLat*PI()/180)*COS(ori.lat*PI()/180)*POW(SIN((ori.shopLng*PI()/180-ori.lng*PI()/180)/2),2)))*1000) AS distance,
                    ori.postmanFinishTime,ori.postmanSureTime,ori.postmanPullTime,
                    (CASE WHEN ori.postmanFinishTime IS NULL THEN 0 WHEN ori.postmanSureTime IS NOT NULL THEN ori.postmanFinishTime -ori.postmanSureTime WHEN ori.postmanPullTime IS NOT NULL THEN ori.postmanFinishTime -ori.postmanPullTime ELSE 0 END)/1000 AS ttime
                FROM order_info AS ori
                LEFT JOIN order_pay AS orp ON orp.orderNo=ori.orderNo
            )a
         */
        Integer postmanType = 1;
        CityPostman postman = CityPostmanMemory.getInstance().getPostman(user.getUsername());
        if (postman != null) {
            postmanType = postman.getType();
        }

//        String sql = "SELECT " +
//                "                SUM(a.postmanMoney) postmanMoney,SUM(distance) distance,AVG(ttime) ttime,SUM((CASE WHEN ttime>" + outTime + " THEN 0 ELSE 1 END)) outTimeCounts,COUNT(orderNo) counts " +
//                "            FROM ( " +
//                "                SELECT ori.orderNo,IFNULL(orp.postmanMoney,0) AS postmanMoney,ori.shopLat,ori.shopLng,ori.lat,ori.lng, " +
//                "                    ROUND(6378.138*2*ASIN(SQRT(POW(SIN((ori.shopLat*PI()/180-ori.lat*PI()/180)/2),2)+COS(ori.shopLat*PI()/180)*COS(ori.lat*PI()/180)*POW(SIN((ori.shopLng*PI()/180-ori.lng*PI()/180)/2),2)))*1000) AS distance, " +
//                "                    ori.postmanFinishTime,ori.postmanSureTime,ori.postmanPullTime, " +
//                "                    (CASE WHEN ori.postmanFinishTime IS NULL THEN 0 WHEN ori.postmanSureTime IS NOT NULL THEN ori.postmanFinishTime -ori.postmanSureTime WHEN ori.postmanPullTime IS NOT NULL THEN ori.postmanFinishTime -ori.postmanPullTime ELSE 0 END)/1000 AS ttime " +
//                "                FROM order_info AS ori " +
//                "                LEFT JOIN order_pay AS orp ON orp.orderNo=ori.orderNo " +
//                "                WHERE ori.createtime BETWEEN " + startTime + " AND " + endTime +
//                "                   and orp.postman='" + user.getUsername() + "'" +
//                "            )a";
        String t_order_info = PojoTables.OrderInfo;
        String t_order_pay = PojoTables.OrderPay;
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
            String dataDbName = OrderBakTaskUtils.getDataDbName();
            if (ValidateUtil.isNotNull(dataDbName)) {
                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
            }
        }

        String sql = "";
        if (postmanType != null && postmanType == POSTMAN_TYPE.POSTMAN_2) {
            sql = "SELECT " +
                    "                SUM(a.postmanMoney) postmanMoney,a.exchangeRate,SUM(distance) distance,AVG(ttime) ttime,SUM((CASE WHEN ttime>" + outTime + " THEN 0 ELSE 1 END)) outTimeCounts,COUNT(orderNo) counts " +
                    "            FROM ( " +
                    "                SELECT ori.orderNo,IFNULL(orp.secondaryPostmanMoney,0) AS postmanMoney,orp.exchangeRate as exchangeRate,ori.shopLat,ori.shopLng,ori.lat,ori.lng, " +
                    "                    ROUND(6378.138*2*ASIN(SQRT(POW(SIN((ori.shopLat*PI()/180-ori.lat*PI()/180)/2),2)+COS(ori.shopLat*PI()/180)*COS(ori.lat*PI()/180)*POW(SIN((ori.shopLng*PI()/180-ori.lng*PI()/180)/2),2)))*1000) AS distance, " +
                    "                    ori.postmanFinishTime,ori.secondaryPostmanSureTime,ori.secondaryPostmanPullTime, " +
                    "                    (CASE WHEN ori.postmanFinishTime IS NULL THEN 0 WHEN ori.secondaryPostmanSureTime IS NOT NULL THEN ori.postmanFinishTime -ori.secondaryPostmanSureTime WHEN ori.secondaryPostmanPullTime IS NOT NULL THEN ori.postmanFinishTime -ori.secondaryPostmanPullTime ELSE 0 END)/1000 AS ttime " +
                    "                FROM " + t_order_info + " AS ori " +
                    "                LEFT JOIN " + t_order_pay + " AS orp ON orp.orderNo=ori.orderNo " +
                    "                WHERE ori.createtime BETWEEN " + startTime + " AND " + endTime +
//                    "                   and orp.secondaryPostman='" + user.getUsername() + "'" +
                    "                   and ori.secondaryPostManUsername='" + user.getUsername() + "'";
            // 配送中取消是不是
            if (SYS_SETTINGS.POSTING_CANCEL_STATISTICS_FINISH != null && SYS_SETTINGS.POSTING_CANCEL_STATISTICS_FINISH == 1) {
                sql += "                   and ((ori.orderStatus =" + ORDER_STATUS.OVER + " or ori.orderStatus = " + ORDER_STATUS.POSTMAN_FINISH + ") " +
                        " or ((ori.orderStatus = " + ORDER_STATUS.CANCEL + " or  ori.orderStatus = " + ORDER_STATUS.CANCEL_BY_SYSTEM + " or ori.orderStatus = " + ORDER_STATUS.PAYED_CANCEL + ") and ori.isPostingCancel = 1)" +
                        ")";
            } else {
                sql += "                   and (ori.orderStatus =" + ORDER_STATUS.OVER + " or ori.orderStatus = " + ORDER_STATUS.POSTMAN_FINISH + ") ";
            }
            sql += "            )a";
        } else {
            sql = "SELECT " +
                    "                SUM(a.postmanMoney) postmanMoney,a.exchangeRate,SUM(distance) distance,AVG(ttime) ttime,SUM((CASE WHEN ttime>" + outTime + " THEN 0 ELSE 1 END)) outTimeCounts,COUNT(orderNo) counts " +
                    "            FROM ( " +
                    "                SELECT ori.orderNo,IFNULL(orp.postmanMoney,0) AS postmanMoney,orp.exchangeRate as exchangeRate,ori.shopLat,ori.shopLng,ori.lat,ori.lng, " +
                    "                    ROUND(6378.138*2*ASIN(SQRT(POW(SIN((ori.shopLat*PI()/180-ori.lat*PI()/180)/2),2)+COS(ori.shopLat*PI()/180)*COS(ori.lat*PI()/180)*POW(SIN((ori.shopLng*PI()/180-ori.lng*PI()/180)/2),2)))*1000) AS distance, " +
                    "                    ori.postmanFinishTime,ori.postmanSureTime,ori.postmanPullTime, " +
                    "                    (CASE WHEN ori.postmanFinishTime IS NULL THEN 0 WHEN ori.postmanSureTime IS NOT NULL THEN ori.postmanFinishTime -ori.postmanSureTime WHEN ori.postmanPullTime IS NOT NULL THEN ori.postmanFinishTime -ori.postmanPullTime ELSE 0 END)/1000 AS ttime " +
                    "                FROM " + t_order_info + " AS ori " +
                    "                LEFT JOIN " + t_order_pay + " AS orp ON orp.orderNo=ori.orderNo " +
                    "                WHERE ori.createtime BETWEEN " + startTime + " AND " + endTime +
//                    "                   and orp.postman='" + user.getUsername() + "'" +
                    "                   and ori.postManUsername='" + user.getUsername() + "'";
            // 配送中取消是不是
            if (SYS_SETTINGS.POSTING_CANCEL_STATISTICS_FINISH != null && SYS_SETTINGS.POSTING_CANCEL_STATISTICS_FINISH == 1) {
                sql += "                   and ((ori.orderStatus =" + ORDER_STATUS.OVER + " or ori.orderStatus = " + ORDER_STATUS.POSTMAN_FINISH + ") " +
                        " or ((ori.orderStatus = " + ORDER_STATUS.CANCEL + " or  ori.orderStatus = " + ORDER_STATUS.CANCEL_BY_SYSTEM + " or ori.orderStatus = " + ORDER_STATUS.PAYED_CANCEL + ") and ori.isPostingCancel = 1)" +
                        ")";
            } else {
                sql += "                   and (ori.orderStatus =" + ORDER_STATUS.OVER + " or ori.orderStatus = " + ORDER_STATUS.POSTMAN_FINISH + ") ";
            }
            sql += "            )a";
        }
        HashMap<String, Object> info = null;
        try {
            info = UserDaoSql.getInstance().queryMapBySql(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        // check null
        if (info.get("postmanMoney") == null) {
            info.put("postmanMoney", 0);
        }
        if (info.get("exchangeRate") == null) {
            info.put("exchangeRate", 0);
        }
        if (info.get("distance") == null) {
            info.put("distance", 0);
        }
        if (info.get("ttime") == null) {
            info.put("ttime", 0);
        }
        if (info.get("outTimeCounts") == null) {
            info.put("outTimeCounts", 0);
        }
        if (info.get("counts") == null) {
            info.put("counts", 0);
        }
        return successResult(info);
    }

    // 订单详情信息
    @RequestMapping(method = RequestMethod.POST, value = "admin/postman/order/statistics")
    @ResponseBody
    public String postOrderStatis(String token, Long startTime, Long endTime, Integer page, Integer size) {

        // 验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        if (user.getUserType() != UserType.POSTMAN) {
        }
        if (startTime == null || endTime == null) {
            return AjaxResult.errorResult("请选择查询时间");
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 12;
        }
        Integer postmanType = 1;
        CityPostman postman = CityPostmanMemory.getInstance().getPostman(user.getUsername());
        if (postman != null) {
            postmanType = postman.getType();
        }
        String sql = "";
//        String sql = "SELECT ori.orderNo, orp.postmanMoney,ori.shopAddress,ori.address,ori.shopLat,ori.shopLng,ori.lat,ori.lng, " +
//                "       (CASE WHEN ori.postmanSureTime IS NOT NULL THEN ori.postmanSureTime ELSE ori.postmanPullTime END) startTime,ori.postmanFinishTime AS endTime, " +
//                "       ROUND(6378.138*2*ASIN(SQRT(POW(SIN((ori.shopLat*PI()/180-ori.lat*PI()/180)/2),2)+COS(ori.shopLat*PI()/180)*COS(ori.lat*PI()/180)*POW(SIN((ori.shopLng*PI()/180-ori.lng*PI()/180)/2),2)))*1000) AS distance, " +
//                "       FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') daTime " +
//                " FROM order_info AS ori " +
//                " LEFT JOIN order_pay AS orp ON orp.orderno=ori.orderno " +
//                " WHERE ori.createtime BETWEEN " + startTime + " AND " + endTime +
//                "    and orp.postman='" + user.getUsername() + "'" +
//                " LIMIT " + ((page - 1) * size) + "," + size;

        String t_order_info = PojoTables.OrderInfo;
        String t_order_pay = PojoTables.OrderPay;
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
            String dataDbName = OrderBakTaskUtils.getDataDbName();
            if (ValidateUtil.isNotNull(dataDbName)) {
                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
            }
        }

        if (postmanType != null && postmanType == POSTMAN_TYPE.POSTMAN_2) {
            sql = "SELECT ori.orderNo, orp.secondaryPostmanMoney as postmanMoney,orp.exchangeRate as exchangeRate,ori.shopAddress,ori.address,ori.shopLat,ori.shopLng,ori.lat,ori.lng, " +
                    "       (CASE WHEN ori.secondaryPostmanSureTime IS NOT NULL THEN ori.secondaryPostmanSureTime ELSE ori.secondaryPostmanPullTime END) startTime,ori.postmanFinishTime AS endTime, " +
                    "       ROUND(6378.138*2*ASIN(SQRT(POW(SIN((ori.shopLat*PI()/180-ori.lat*PI()/180)/2),2)+COS(ori.shopLat*PI()/180)*COS(ori.lat*PI()/180)*POW(SIN((ori.shopLng*PI()/180-ori.lng*PI()/180)/2),2)))*1000) AS distance, " +
                    "       FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') daTime " +
                    " FROM " + t_order_info + " AS ori " +
                    " LEFT JOIN " + t_order_pay + " AS orp ON orp.orderno=ori.orderno " +
                    " WHERE ori.createtime BETWEEN " + startTime + " AND " + endTime +
                    "    and orp.secondaryPostman='" + user.getUsername() + "'" +
                    " LIMIT " + ((page - 1) * size) + "," + size;
        } else {
            sql =
                    "SELECT ori.orderNo, orp.postmanMoney,orp.exchangeRate as exchangeRate,ori.shopAddress,ori.address,ori.shopLat,ori.shopLng,ori.lat,ori.lng, " +
                            "       (CASE WHEN ori.postmanSureTime IS NOT NULL THEN ori.postmanSureTime ELSE ori.postmanPullTime END) startTime,ori.postmanFinishTime AS endTime, " +
                            "       ROUND(6378.138*2*ASIN(SQRT(POW(SIN((ori.shopLat*PI()/180-ori.lat*PI()/180)/2),2)+COS(ori.shopLat*PI()/180)*COS(ori.lat*PI()/180)*POW(SIN((ori.shopLng*PI()/180-ori.lng*PI()/180)/2),2)))*1000) AS distance, " +
                            "       FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') daTime " +
                            " FROM " + t_order_info + " AS ori " +
                            " LEFT JOIN " + t_order_pay + " AS orp ON orp.orderno=ori.orderno " +
                            " WHERE ori.createtime BETWEEN " + startTime + " AND " + endTime +
                            "    and ori.orderNo not like 'S%'" +
                            "    and ori.postmanUsername='" + user.getUsername() + "'" +
                            "    and ori.orderStatus=" + ORDER_STATUS.OVER + "" +
                            "    and ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + "" +
                            " LIMIT " + ((page - 1) * size) + "," + size;
        }

        List<HashMap<String, Object>> list = null;
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        if (list != null && !list.isEmpty()) {
            for (int i = 0; i < list.size(); i++) {

            }
        }

        return AjaxResult.successResult(list.size(), list);
    }

    /**
     * 配送员管理后台 每天收入统计
     *
     * @param token
     * @param postmanUsername
     * @param startTime
     * @param endTime
     * @param page
     * @param size
     * @return
     */
    @RequestMapping(value = "admin/postman/order/statistics/byDay")
    @ResponseBody
    public String postmanMoneyOnDay(String token, String postmanUsername, Long startTime, String province, String city, String county, String region, Long endTime, Integer page, Integer size) {

        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        //  只有城代可查看
//        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_ADMIN) {
        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_ADMIN && user.getUserType() != UserType.COMPANY && user.getUserType() != UserType.SUPER_COMPANY) {
            return successResult(null, 0);
        }
//        CityAgent cityAgent = null;
//        if (user.getUserType() == UserType.ADMIN) {
//            cityAgent = CityAgentMemory.getInstance().getByUsername(user.getUsername());
//            if (cityAgent == null) {
//                return AjaxResult.errorResult("代理管辖的商圈错误！");
//            }
//        }
        Set<Long> agentIdSet = null;

        if (startTime == null) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null) {
            endTime = new Date().getTime();
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 12;
        }
        Set<String> uuu = null;
        if (StringUtils.isNotEmpty(postmanUsername)) {
            uuu = Stream.of(postmanUsername.split(",")).collect(Collectors.toSet());
        }
        /* 订单收入查询-配送员配送费
            SELECT a.* FROM (
                SELECT DISTINCT cpm.username,COUNT(ori.orderno) orderCount,
                SUM(IFNULL(orp.postmanMoney,0)) postmanMoney
                FROM city_postman AS cpm
                LEFT JOIN order_pay AS orp ON orp.postman=cpm.username
                LEFT JOIN order_info AS ori ON orp.orderno=ori.orderno
                LEFT JOIN postman_agent_relation AS par ON par.postmanUsername=cpm.username
                WHERE ori.delFlag=0 AND par.agentId=9
                GROUP BY cpm.username
            )a
         */

//        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
//            if (ValidateUtil.isNotNull(city)) {
//                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
//            }
//        }

        String t_order_info = PojoTables.OrderInfo;
        String t_order_pay = PojoTables.OrderPay;
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
            String dataDbName = OrderBakTaskUtils.getDataDbName();
            if (ValidateUtil.isNotNull(dataDbName)) {
                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
            }
        }

        String sql = "SELECT a.* FROM (   SELECT DISTINCT cpm.username,cag.province,cag.city,cag.county,COUNT(orp.orderno) orderCount, " +
                "SUM(IFNULL(orp.postmanMoney,0)) money, " +
                "IFNULL(uwt.popTotalMoney,0) popTotalMoney," +
                "IFNULL(uwt.popMoney,0) popMoney " +
                "FROM " + PojoTables.CityPostman + " AS cpm                  " +
                " LEFT JOIN " + t_order_pay + " AS orp ON orp.postman = cpm.username  AND orp.delFlag=0  AND orp.createTime BETWEEN  " + startTime + "  AND  " + endTime +
                " LEFT JOIN " + PojoTables.POSTMAN_AGENT_RELATION + " AS par ON par.postmanUsername=cpm.username AND par.delflag=0" +
                " LEFT JOIN " + PojoTables.UserInfo + " AS ufo ON cpm.username=ufo.username                 " +
                " LEFT JOIN " + PojoTables.USER_WALLET + " AS uwt ON uwt.username=ufo.username AND uwt.delflag=0 " +
                " LEFT JOIN " + PojoTables.CITY_AGENT + " AS cag ON cag.id=par.agentId                 " +
                " WHERE cpm.delFlag=0 AND ufo.delFlag=0 ";
        if (agentIdSet != null && !agentIdSet.isEmpty()) {
            sql += " AND par.agentId in(" + StringUtils.join(agentIdSet, ",") + ") ";
        }
        sql +=
//                "                       " + (cityAgent != null ? ("AND par.agentId= " + cityAgent.getId()) : "") +
                "                       " + (StringUtils.isNotEmpty(region) ? ("AND par.agentId= " + region) : "") +
                        "                   " + (StringUtils.isNotEmpty(province) ? " AND cag.province='" + province + "'" : "") +
                        "                   " + (StringUtils.isNotEmpty(city) ? " AND cag.city='" + city + "'" : "") +
                        "                   " + (StringUtils.isNotEmpty(county) ? " AND cag.county='" + county + "'" : "") +
                        "                GROUP BY cpm.username ORDER BY orderCount DESC" +
                        "            )a " +
                        "           " + (uuu == null ? "" : (uuu.size() == 1 ? (" WHERE a.username='" + postmanUsername + "'") : " WHERE a.username in('" + StringUtils.join(uuu, "','") + "')")) +
                        "   LIMIT " + ((page - 1) * size) + "," + size;
        String sqlCount = "SELECT COUNT(1) COUNTS FROM ( " +
                "                SELECT DISTINCT cpm.username,COUNT(orp.orderno) orderCount, " +
                "                SUM(IFNULL(orp.postmanMoney,0)) postmanMoney, " +
                "                SUM(IFNULL(uwt.popMoney,0)) popMoney " +
                "                FROM " + PojoTables.CityPostman + " AS cpm                  " +
                " LEFT JOIN " + t_order_pay + " AS orp ON orp.postman = cpm.username  AND orp.delFlag=0  and orp.createTime BETWEEN  " + startTime + "  AND  " + endTime +
                " LEFT JOIN " + PojoTables.POSTMAN_AGENT_RELATION + " AS par ON par.postmanUsername=cpm.username  AND par.delflag=0 " +
                " LEFT JOIN " + PojoTables.UserInfo + " AS ufo ON cpm.username=ufo.username   " +
                " LEFT JOIN " + PojoTables.USER_WALLET + " AS uwt ON uwt.username=ufo.username  AND uwt.delflag=0 " +
                " LEFT JOIN " + PojoTables.CITY_AGENT + " as cag on cag.id=par.agentId  " +
                " WHERE cpm.delFlag=0 and ufo.delFlag=0 ";
        if (agentIdSet != null && !agentIdSet.isEmpty()) {
            sqlCount += " AND par.agentId in(" + StringUtils.join(agentIdSet, ",") + ") ";
        }
        sqlCount +=
                "                       " + (StringUtils.isNotEmpty(region) ? ("AND par.agentId= " + region) : "") +
                        "                   " + (StringUtils.isNotEmpty(province) ? " AND cag.province='" + province + "'" : "") +
                        "                   " + (StringUtils.isNotEmpty(city) ? " AND cag.city='" + city + "'" : "") +
                        "                   " + (StringUtils.isNotEmpty(county) ? " AND cag.county='" + county + "'" : "") +
                        "                GROUP BY cpm.username " +
                        "            )a " +
                        "           " + (uuu == null ? "" : (uuu.size() == 1 ? (" WHERE a.username='" + postmanUsername + "'") : " WHERE a.username in('" + StringUtils.join(uuu, "','") + "')"));

        List<HashMap<String, Object>> list = null;
        int userNum = 0;
        int orderNum = 0;
        long count = 0;

        try {
            list = UserDaoSql.getInstance().queryBySql(sql);
            count = UserDaoSql.getInstance().getCount(sqlCount);
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        if (list != null && !list.isEmpty()) {
            for (int i = 0; i < list.size(); i++) {
                // 添加配送员信息
                String username = list.get(i).get("username").toString();
                UserWallet userWallet = UserWalletMemory.getInstance().getWallet(username);
                list.get(i).put("wallet", userWallet == null ? 0D : userWallet.getMoney());
                list.get(i).put("fcMoney", userWallet == null ? 0D : userWallet.getFcMoney());
                UserInfo userInfo = UserInfoMemory.getInstance().getUserInfo(username);
                list.get(i).put("postName", userInfo == null ? "" : userInfo.getName());
                list.get(i).put("icon", userInfo != null && StringUtils.isNotEmpty(userInfo.getSignPhoto()) && !userInfo.getSignPhoto().startsWith("http") ? SYS_CONFIG_PATH.IMAGE_BASE_URL + userInfo.getSignPhoto() : userInfo.getSignPhoto());

                // 获取配送员配送城市
                CityPostman cityPostman = CityPostmanMemory.getInstance().getPostman(username);
                list.get(i).put("province", (cityPostman == null ? "" : cityPostman.getProvince()));
                list.get(i).put("city", (cityPostman == null ? "" : cityPostman.getCity()));
                // 获取配送员配送商圈名称
                Long agentId = PostmanAgentRelationMemory.getInstance().getAgentByPostmanUsername(username);
                CityAgent cityAgent1 = CityAgentMemory.getInstance().getById(agentId);
                list.get(i).put("cityAgent", cityAgent1 == null ? "" : cityAgent1.getName());

                userNum += 1;
                orderNum += Integer.parseInt(list.get(i).get("orderCount").toString());
            }
        }
        Map info = new HashMap();
        info.put("userNum", userNum);
        info.put("orderNum", orderNum);
//        String info = {"userNum":userNum, "orderNum":orderNum};
        JSONObject obj = new JSONObject();
        obj.put("status", 1);
        obj.put("totalCount", count);
        obj.put("data", JSONArray.toJSON(list == null ? Collections.emptyList() : list));
        obj.put("info", JSONObject.toJSON(info));
        return obj.toString();
//        return AjaxResult.successResult(list, count, info);
    }

    @RequestMapping(value = "admin/company/postman/order/statistics")
    @ResponseBody
    public String post_company_postman_money(String token, String postmanUsername, Long startTime, String province, String city, String county, Long endTime, Integer page, Integer size) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }

        String t_order_info = PojoTables.OrderInfo;
        String t_order_pay = PojoTables.OrderPay;
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
            String dataDbName = OrderBakTaskUtils.getDataDbName();
            if (ValidateUtil.isNotNull(dataDbName)) {
                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
            }
        }

        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        String sql = "SELECT DISTINCT cp.id id,cp.username,cp.province,cp.city,cp.county,ui.name,ui.signPhoto, " +
                "IFNULL(pcsi.money,0)+IFNULL(pcsi.allowance,0)-IFNULL(pcsi.applyMoney,0) money,  " +
                "SUM(CASE WHEN ori.grabCompanyId=pc.id  THEN 1 ELSE 0 END) orderCount,  " +
                "SUM(CASE WHEN ori.grabCompanyId=psc.companyId AND ori.postmanUsername=psc.username THEN IFNULL(orp.postmanMoney,0) ELSE 0 END) postmanMoney  " +
                "FROM (SELECT DISTINCT companyId,username FROM " + PojoTables.POSTMAN_SIGN_COMPANY + " WHERE STATUS=9 AND delFlag=0) psc " +
                "LEFT JOIN " + PojoTables.CityPostman + " cp ON cp.username=psc.username AND cp.delFlag=0 " +
                "LEFT JOIN " + PojoTables.POST_COMPANY + " pc ON pc.id=psc.companyId AND pc.delFlag=0  " +
                "LEFT JOIN " + PojoTables.UserInfo + " ui ON ui.username=cp.username AND ui.delFlag=0  " +
                "LEFT JOIN " + t_order_info + " ori ON ori.postmanUsername=psc.username AND ori.delFlag=0 AND ori.orderStatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " " +
                (null != startTime && null != endTime ? (" AND ori.createtime BETWEEN " + startTime + " AND " + endTime + " ") : "") +
                "LEFT JOIN " + t_order_pay + " orp ON orp.orderNo=ori.orderNo AND orp.delFlag=0  " +
                "LEFT JOIN " + PojoTables.POST_COMPANY_SALARY_INFO + " pcsi ON pcsi.username=cp.username AND pcsi.companyId=pc.id AND pcsi.delFlag=0 " +
                "WHERE cp.delFlag=0 AND pc.username='" + user.getUsername() + "' " +
                (StringUtils.isNotEmpty(province) ? (" AND cp.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND cp.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND cp.county='" + county + "'") : "") +
                (StringUtils.isNotEmpty(postmanUsername) ? (" AND cp.username='" + postmanUsername + "'") : "") +
                " GROUP BY id" +
                " limit " + ((page - 1) * size) + "," + size;

        String count = "select count(a.id) counts from (SELECT DISTINCT cp.id id,cp.username,cp.province,cp.city,cp.county,ui.name,ui.signPhoto, " +
                "IFNULL(pcsi.money,0)+IFNULL(pcsi.allowance,0)-IFNULL(pcsi.applyMoney,0) money,  " +
                "SUM(CASE WHEN ori.grabCompanyId=pc.id  THEN 1 ELSE 0 END) orderCount,  " +
                "SUM(CASE WHEN ori.grabCompanyId=psc.companyId AND ori.postmanUsername=psc.username THEN IFNULL(orp.postmanMoney,0) ELSE 0 END) postmanMoney  " +
                "FROM (SELECT DISTINCT companyId,username FROM " + PojoTables.POSTMAN_SIGN_COMPANY + " WHERE STATUS=9 AND delFlag=0) psc " +
                "LEFT JOIN  " + PojoTables.CityPostman + " cp ON cp.username=psc.username AND cp.delFlag=0 " +
                "LEFT JOIN  " + PojoTables.POST_COMPANY + " pc ON pc.id=psc.companyId AND pc.delFlag=0  " +
                "LEFT JOIN  " + PojoTables.UserInfo + " ui ON ui.username=cp.username AND ui.delFlag=0  " +
                "LEFT JOIN  " + t_order_info + " ori ON ori.postmanUsername=psc.username AND ori.delFlag=0 AND ori.orderStatus=9 AND ori.isRefundApply!=2 " +
                (null != startTime && null != endTime ? (" AND ori.createtime BETWEEN " + startTime + " AND " + endTime + " ") : "") +
                "LEFT JOIN  " + t_order_pay + " orp ON orp.orderNo=ori.orderNo AND orp.delFlag=0  " +
                "LEFT JOIN  " + PojoTables.POST_COMPANY_SALARY_INFO + " pcsi ON pcsi.username=cp.username AND pcsi.companyId=pc.id AND pcsi.delFlag=0 " +
                "WHERE cp.delFlag=0 AND pc.username='" + user.getUsername() + "' " +
                (StringUtils.isNotEmpty(province) ? (" AND cp.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND cp.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND cp.county='" + county + "'") : "") +
                (StringUtils.isNotEmpty(postmanUsername) ? (" AND cp.username='" + postmanUsername + "'") : "") +
                " GROUP BY id) a";
        List<HashMap<String, Object>> list = null;
        Long totalCount = 0l;
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);
            totalCount = UserDaoSql.getInstance().getCount(count);
        } catch (Exception e) {
            return AjaxResult.errorResult(e.getMessage());
        }
        return AjaxResult.successResult(list, totalCount);
    }

    //todo: yiyi 配送员提现统计
    @RequestMapping(value = "admin/company/postman/pop/statistics")
    @ResponseBody
    public String post_company_postman_pop(String token, String postmanUsername, String province, String city, String county, Long region, Integer page, Integer size) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }

        Set<Long> agentIdSet = null;
        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        String sql = "SELECT DISTINCT cp.id id,cp.username,cp.province,cp.city,cp.county,ui.name postName,ui.signPhoto icon,ca.name cityAgent,  " +
                "SUM(IFNULL(pcsi.money,0)) wallet,SUM(IFNULL(pcsi.payedMoney,0)) popTotalMoney,SUM(IFNULL(pcsi.applyMoney,0)) popMoney,  " +
                "SUM(IFNULL(pcsi.allowance,0)) allowance  " +
                "FROM " + PojoTables.CityPostman + " cp   " +
                "LEFT JOIN " + PojoTables.POSTMAN_AGENT_RELATION + " par ON par.postmanUsername=cp.username AND par.delFlag=0  " +
                "LEFT JOIN " + PojoTables.CITY_AGENT + " ca ON ca.id=par.agentId AND ca.delFlag=0  " +
                "LEFT JOIN " + PojoTables.UserInfo + " ui ON ui.username=cp.username AND ui.delFlag=0  " +
                "LEFT JOIN " + PojoTables.POST_COMPANY_SALARY_INFO + " pcsi ON pcsi.username=cp.username AND pcsi.delFlag=0  " +
                "WHERE cp.delFlag=0  " +
                (StringUtils.isNotEmpty(province) ? (" AND cp.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND cp.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND cp.county='" + county + "'") : "") +
                (StringUtils.isNotEmpty(postmanUsername) ? (" AND cp.username='" + postmanUsername + "'") : "");
        if (agentIdSet != null && !agentIdSet.isEmpty()) {
            sql += " AND par.agentId in(" + StringUtils.join(agentIdSet, ",") + ") ";
        }
        sql += (null != region ? (" AND ca.id=" + region + " ") : "") +
                " GROUP BY id" +
                " limit " + ((page - 1) * size) + "," + size;

        String count = "select count(a.id) counts from (SELECT DISTINCT cp.id id,cp.username,cp.province,cp.city,cp.county,ui.name,ui.signPhoto,ca.name agentName,  " +
                "SUM(IFNULL(pcsi.money,0)) money,SUM(IFNULL(pcsi.payedMoney,0)) payedMoney,SUM(IFNULL(pcsi.applyMoney,0)) applyMoney,  " +
                "SUM(IFNULL(pcsi.allowance,0)) allowance  " +
                "FROM " + PojoTables.CityPostman + " cp   " +
                "LEFT JOIN " + PojoTables.POSTMAN_AGENT_RELATION + " par ON par.postmanUsername=cp.username AND par.delFlag=0  " +
                "LEFT JOIN " + PojoTables.CITY_AGENT + " ca ON ca.id=par.agentId AND ca.delFlag=0  " +
                "LEFT JOIN " + PojoTables.UserInfo + " ui ON ui.username=cp.username AND ui.delFlag=0  " +
                "LEFT JOIN " + PojoTables.POST_COMPANY_SALARY_INFO + " pcsi ON pcsi.username=cp.username AND pcsi.delFlag=0  " +
                "WHERE cp.delFlag=0  " +
                (StringUtils.isNotEmpty(province) ? (" AND cp.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND cp.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND cp.county='" + county + "'") : "") +
                (StringUtils.isNotEmpty(postmanUsername) ? (" AND cp.username='" + postmanUsername + "'") : "") +
                (null != region ? (" AND ca.id=" + region + " ") : "");
        if (agentIdSet != null && !agentIdSet.isEmpty()) {
            count += " AND par.agentId in(" + StringUtils.join(agentIdSet, ",") + ") ";
        }
        count += " GROUP BY id) a";
        List<HashMap<String, Object>> list = null;
        Long totalCount = 0l;
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);
            totalCount = UserDaoSql.getInstance().getCount(count);
        } catch (Exception e) {
            return AjaxResult.errorResult(e.getMessage());
        }
        return AjaxResult.successResult(list, totalCount);
    }

    // 骑手受罚订单
    @RequestMapping(value = "admin/company/postman/order/punish")
    @ResponseBody
    public String post_company_order_shoufa(String token, Long startTime, Long endTime, String province, String city, String county, String orderNo, Integer page, Integer size) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }

        String t_order_info = PojoTables.OrderInfo;
        String t_order_pay = PojoTables.OrderPay;
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
            String dataDbName = OrderBakTaskUtils.getDataDbName();
            if (ValidateUtil.isNotNull(dataDbName)) {
                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
            }
        }

        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        String select = "select ori.orderNo,ori.createTime,ori.postManName,ori.postmanUsername,  " +
                "ori.postmanFinishTime,ori.agentPostmanOverTime,ori.ziQuTime  ";
        String sql = "from " + t_order_info + " ori   " +
                "left join " + PojoTables.POSTMAN_SIGN_COMPANY + " psc on psc.username=ori.postmanUsername and psc.delFlag=0  " +
                "left join " + PojoTables.POST_COMPANY + " pc on pc.id=psc.companyId and pc.delFlag=0  " +
                "where ori.delFlag=0 and ori.orderStatus=" + ORDER_STATUS.OVER + " and pc.id=ori.grabCompanyId and pc.username='" + user.getUsername() + "'  " +
                (StringUtils.isNotEmpty(province) ? (" AND ori.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND ori.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND ori.county='" + county + "'") : "") +
                (StringUtils.isNotEmpty(orderNo) ? (" AND ori.orderNo='" + orderNo + "'") : "") +
                (null != startTime && null != endTime ? (" AND ori.createtime BETWEEN " + startTime + " AND " + endTime + " ") : "") +
                " and ((ori.agentPostmanOverTime > 0 and ((ori.postmanFinishTime - ori.createTime) > (ori.agentPostmanOverTime * 60 * 1000))) " +
                "      or (ori.ziQuTime > 0 and ori.postmanFinishTime > ori.ziQuTime))";
        String limit = " limit " + ((page - 1) * size) + "," + size;
        String count = "select count(ori.orderNo) counts ";

        List<HashMap<String, Object>> list = null;
        Long totalCount = 0l;
        try {
            String sqlSelect = select + sql + limit;
            String countSelect = count + sql;
            list = UserDaoSql.getInstance().queryBySql(sqlSelect);
            totalCount = UserDaoSql.getInstance().getCount(countSelect);
        } catch (Exception e) {
            return AjaxResult.errorResult(e.getMessage());
        }
        return AjaxResult.successResult(list, totalCount);
    }
}
